﻿using Dapper;
using guidHelper;
using Microsoft.Data.SqlClient;
using MyDapperApi.Models;
using MySql.Data.MySqlClient;
using QYWXUpdate.DataModel;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Reflection;
using System.Text;

namespace MyDapperApi.Services;
/// <summary>
/// 通用数据库查询方法
/// </summary>
public class DateBaseService : IDateBaseService
{
    private readonly IConfiguration _configuration;


    public DateBaseService(IConfiguration configuration)
    {
        _configuration = configuration;
    }

    private IDbConnection CreateConnection(int type = 0, string db = "DefaultConnection")
    {
        switch (type)
        {
            case 0:
                return new MySqlConnection(_configuration.GetConnectionString(db));
            case 1:
                return new SqlConnection(_configuration.GetConnectionString(db));
            default:
                return new MySqlConnection(_configuration.GetConnectionString("DefaultConnection"));
        }
    }

    public class SqlBuilder
    {
        public string Sql { get; set; } = "";
        public Dictionary<string, object> Parameters { get; set; } = new Dictionary<string, object>();

        // 改进Add方法，直接存储参数名和值
        public virtual void Add(string conditionSql, string paramName, object paramValue)
        {
            Sql += " " + conditionSql + " ";
            Parameters[paramName] = paramValue;
        }
        public virtual void AddGroupby(string groupbySql)
        {
            Sql += " " + groupbySql + " ";
        }

        public virtual void AddOrderBy(string orderbySql)
        {
            Sql += " " + orderbySql + " ";
        }

        public virtual void AddPage(int pageIndex, int pageSize, int type = 0)
        {
            if (type == 0)//mysql
            {
                Sql += $"limit {(pageIndex - 1) * pageSize},{pageSize} ";
            }
            if (type == 1)//sqlserver
            {
                Sql += $"OFFSET ({pageIndex} - 1) * {pageSize} ROWS FETCH NEXT {pageSize} ROWS ONLY";
            }
        }
    }

    public async Task<T> QueryFirstOrDefaultAsync<T>(string sql, SqlBuilder sqlBuilder, int type = 0, string db = "DefaultConnection")
    {
        {
            using var connection = CreateConnection(type, db);

            var parameters = new DynamicParameters();

            foreach (var param in sqlBuilder.Parameters)
            {
                parameters.Add(param.Key, param.Value);
            }
            // 执行查询
            var res = await connection.QueryFirstOrDefaultAsync<T>(
                sql + sqlBuilder.Sql,
                parameters
            );

            if (res == null)
            {
                res = default;
            }

            return res;
        }
    }

    public async Task<List<T>> QueryAsync<T>(string sql, SqlBuilder sqlBuilder, int type = 0, string db = "DefaultConnection")
    {
        using var connection = CreateConnection(type, db);

        var parameters = new DynamicParameters();

        foreach (var param in sqlBuilder.Parameters)
        {
            parameters.Add(param.Key, param.Value);
        }
        // 执行查询
        var res = await connection.QueryAsync<T>(
            sql + sqlBuilder.Sql,
            parameters
        );

        return res.ToList();
    }

    public async Task<CommonResponse> ExecuteAsync(string sql, SqlBuilder? sqlBuilder = null, int type = 0, string db = "DefaultConnection")
    {
        var result = new CommonResponse();
        using var connection = CreateConnection(type, db);
        var parameters = new DynamicParameters();
        if (sqlBuilder != null)
        {
            foreach (var param in sqlBuilder.Parameters)
            {
                parameters.Add(param.Key, param.Value);
            }
        }

        var res = await connection.ExecuteAsync(sql, parameters);

        result.SetSuccess(res);

        return result;
    }

    /// <summary>
    /// 只更新实体中提供了值的字段（ID必须提供）
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="entity">实体（包含ID和需要更新的字段）</param>
    /// <param name="dbtype">数据库类型</param>
    /// <param name="db">数据库名称</param>
    /// <param name="transaction">事务</param>
    /// <param name="commandTimeout">超时</param>
    /// <returns></returns>
    public async Task<CommonResponse> UpdateSpecifiedFields<T>(T entity,
                              int dbtype = 0, string db = "DefaultConnection",
                              IDbTransaction transaction = null,
                              int? commandTimeout = null) where T : class
    {
        var result = new CommonResponse();

        if (entity == null)
            throw new ArgumentNullException(nameof(entity));

        using var connection = CreateConnection(dbtype, db);
        var type = typeof(T);

        // 获取表名
        var tableAttr = type.GetCustomAttribute<TableAttribute>();
        if (tableAttr == null || string.IsNullOrEmpty(tableAttr.Name))
            throw new InvalidOperationException($"实体 {type.Name} 未定义Table特性或表名");
        string tableName = tableAttr.Name;

        // 获取所有属性及其列名和值
        var properties = type.GetProperties()
            .Where(p => p.CanRead && p.CanWrite)
            .Select(p => new
            {
                Property = p,
                ColumnName = p.GetCustomAttribute<ColumnAttribute>()?.Name ?? p.Name,
                Value = p.GetValue(entity),
                DefaultValue = p.PropertyType.IsValueType ? Activator.CreateInstance(p.PropertyType) : null
            })
            .ToList();

        // 查找主键ID属性
        var idProperty = properties.FirstOrDefault(p =>
            string.Equals(p.ColumnName, "id", StringComparison.OrdinalIgnoreCase));

        if (idProperty == null)
            throw new InvalidOperationException($"实体 {type.Name} 未找到主键列（id）");

        // 检查ID是否提供（不能是默认值）
        if (Equals(idProperty.Value, idProperty.DefaultValue))
            throw new ArgumentException("必须提供ID值", nameof(entity));

        // 找出提供了值的字段（排除ID本身，且值不等于默认值）
        var specifiedFields = properties
            .Where(p => p != idProperty && !Equals(p.Value, p.DefaultValue))
            .Select(p => $"{p.ColumnName} = @{p.Property.Name}")
            .ToList();

        if (!specifiedFields.Any())
        {
            //result.SetError("没有提供需要更新的字段");
            return result;
        }

        // 构建SQL语句
        string sql = $"UPDATE {tableName} SET {string.Join(", ", specifiedFields)} WHERE {idProperty.ColumnName} = @{idProperty.Property.Name}";

        // 执行更新
        var res = await connection.ExecuteAsync(sql, entity, transaction, commandTimeout);

        if (res == 1)
        {
            result.SetSuccess(specifiedFields.Count);
        }
        else
        {
            result.SetError("sql执行失败或未找到匹配的记录");
        }

        return result;
    }
    /// <summary>
    /// 创建日志接口
    /// </summary>
    /// <param name="methodName"></param>
    /// <param name="className"></param>
    /// <param name="logMessage"></param>
    /// <param name="type"></param>
    public async void CreateLog(string methodName, string className, string logMessage, int type)
    {
        var sqlBuilder = new SqlBuilder();
        sqlBuilder.Add("", "Id", ToolHelper.GUIDTO16().ToString());
        sqlBuilder.Add("", "Method", methodName);
        sqlBuilder.Add("", "Class", className);
        sqlBuilder.Add("", "Message", logMessage);
        sqlBuilder.Add("", "CreatedOn", DateTime.Now.ToString());
        if (type == 1)
        {
            sqlBuilder.Add("", "Type", "Success");
        }
        if (type == 2)
        {
            sqlBuilder.Add("", "Type", "Fail");
        }
        var sql = "INSERT INTO log_msg (id, method, class, message, Created_On,type) VALUES (@Id, @Method, @Class, @Message, @CreatedOn,@Type);";

        await ExecuteAsync(sql, sqlBuilder);
    }
    /// <summary>
    /// 基础插入方法
    /// </summary>
    /// <typeparam name="T">表实体类型</typeparam>
    /// <param name="entity">表实体</param>
    /// <param name="dbtype">数据库类型</param>
    /// <param name="db">数据库名称</param>
    /// <returns></returns>
    /// <exception cref="ArgumentNullException"></exception>
    /// <exception cref="InvalidOperationException"></exception>
    /// <exception cref="ArgumentException"></exception>
    public async Task<CommonResponse> AddBaseService<T>(T entity, int dbtype = 0, string db = "DefaultConnection")
    {
        var result = new CommonResponse();

        if (entity == null)
            throw new ArgumentNullException(nameof(entity));

        using var connection = CreateConnection(dbtype, db);
        var type = typeof(T);

        // 获取表名
        var tableAttr = type.GetCustomAttribute<TableAttribute>();
        if (tableAttr == null || string.IsNullOrEmpty(tableAttr.Name))
            throw new InvalidOperationException($"实体 {type.Name} 未定义Table特性或表名");
        string tableName = tableAttr.Name;

        // 获取所有属性及其列名和值
        var properties = type.GetProperties()
            .Where(p => p.CanRead && p.CanWrite)
            .Select(p => new
            {
                Property = p,
                ColumnName = p.GetCustomAttribute<ColumnAttribute>()?.Name ?? p.Name,
                Value = p.GetValue(entity),
                DefaultValue = p.PropertyType.IsValueType ? Activator.CreateInstance(p.PropertyType) : null
            })
            .ToList();

        // 查找主键ID属性
        var idProperty = properties.FirstOrDefault(p =>
            string.Equals(p.ColumnName, "id", StringComparison.OrdinalIgnoreCase));

        if (idProperty == null)
            throw new InvalidOperationException($"实体 {type.Name} 未找到主键列（id）");

        // 检查ID是否提供（不能是默认值）
        if (Equals(idProperty.Value, idProperty.DefaultValue))
            throw new ArgumentException("必须提供ID值", nameof(entity));

        // 找出提供了值的字段（排除ID本身，且值不等于默认值）
        var specifiedFieldsName = properties
    .Where(p =>
        // 对于值类型，即使值等于默认值也包含
        (p.Property.PropertyType.IsValueType && p.Value != null) ||
        // 对于引用类型，值不等于默认值（null）才包含
        (!p.Property.PropertyType.IsValueType && !Equals(p.Value, p.DefaultValue))
    )
    .Select(p => $"{p.ColumnName}")
    .ToList();

        var specifiedFieldsValue = properties
            .Where(p =>
                (p.Property.PropertyType.IsValueType && p.Value != null) ||
                (!p.Property.PropertyType.IsValueType && !Equals(p.Value, p.DefaultValue))
            )
            .Select(p => $"{p.Property.Name}")
            .ToList();

        if (!specifiedFieldsName.Any())
        {
            //result.SetError("没有提供需要更新的字段");
            return result;
        }

        string sql = $"INSERT INTO {tableName} ({string.Join(", ", specifiedFieldsName)}) VALUES ({string.Join(", ", specifiedFieldsValue.Select(field => $"@{field}"))})";

        // 执行插入
        var res = await connection.ExecuteAsync(sql, entity);

        if (res == 1)
        {
            result.SetSuccess(specifiedFieldsName.Count);
        }
        else
        {
            result.SetError("sql执行失败或未找到匹配的记录");
        }

        return result;
    }
    /// <summary>
    /// 通用分页接口
    /// </summary>
    /// <typeparam name="T">表实体类型</typeparam>
    /// <param name="entity">表实体(用于获取表结构信息)</param>
    /// <param name="pageIndex">页码(从1开始)</param>
    /// <param name="pageSize">页大小</param>
    /// <param name="PagingType">查询条件字典(key:查询字段, value:查询值),可为空</param>
    /// <param name="groupBy">groupBy语句</param>
    /// <param name="orderBy">orderBy语句</param>
    /// <param name="dbtype">数据库类型</param>
    /// <param name="db">数据库名称</param>
    /// <returns>包含分页数据的响应</returns>
    public async Task<CommonResponse> PagingBaseService<T>(T entity,
                                                          int pageIndex, int pageSize,
                                                          Dictionary<string, string> PagingType = null,
                                                          string groupBy = "", string orderBy = "",
                                                          int dbtype = 0, string db = "DefaultConnection")
    {
        var result = new CommonResponse();

        // 参数验证
        if (entity == null)
            throw new ArgumentNullException(nameof(entity));
        if (pageIndex < 1)
            throw new ArgumentOutOfRangeException(nameof(pageIndex), "页码必须大于等于1");
        if (pageSize < 1)
            throw new ArgumentOutOfRangeException(nameof(pageSize), "页大小必须大于等于1");

        using var connection = CreateConnection(dbtype, db);
        var type = typeof(T);

        // 获取表名
        var tableAttr = type.GetCustomAttribute<TableAttribute>();
        if (tableAttr == null || string.IsNullOrEmpty(tableAttr.Name))
            throw new InvalidOperationException($"实体 {type.Name} 未定义Table特性或表名");
        string tableName = EscapeSqlIdentifier(tableAttr.Name, dbtype);

        // 获取所有属性及其元数据
        var propertyMetadata = type.GetProperties()
            .Where(p => p.CanRead)
            .Select(p => new
            {
                PropertyName = p.Name,
                ColumnName = EscapeSqlIdentifier(p.GetCustomAttribute<ColumnAttribute>()?.Name ?? p.Name, dbtype),
                PropertyType = p.PropertyType,
                // 处理可空类型
                UnderlyingType = Nullable.GetUnderlyingType(p.PropertyType) ?? p.PropertyType
            })
            .ToList();

        // 构建查询字段（默认查询所有字段）
        var selectFields = "*";

        // 构建查询条件
        var whereConditions = new List<string>();
        var parameters = new DynamicParameters();

        if (PagingType != null && PagingType.Count > 0)
        {
            foreach (var (field, value) in PagingType)
            {
                // 跳过空值条件
                if (string.IsNullOrEmpty(value))
                    continue;

                // 查找字段对应的属性元数据
                var propMeta = propertyMetadata.FirstOrDefault(p =>
                    string.Equals(p.PropertyName, field, StringComparison.OrdinalIgnoreCase));

                // 获取转义后的列名
                string columnName = propMeta != null
                    ? propMeta.ColumnName
                    : EscapeSqlIdentifier(field, dbtype);

                // 生成唯一参数名
                var paramName = $"@param_{Guid.NewGuid().ToString("N").Substring(0, 8)}";

                // 尝试将值转换为对应的数据类型
                object paramValue;
                if (propMeta != null)
                {
                    try
                    {
                        // 根据属性类型进行转换
                        paramValue = Convert.ChangeType(value, propMeta.UnderlyingType);
                    }
                    catch (Exception ex)
                    {
                        throw new ArgumentException(
                            $"查询条件 '{field}' 的值 '{value}' 无法转换为 {propMeta.UnderlyingType.Name} 类型",
                            ex);
                    }
                }
                else
                {
                    // 未知字段，作为字符串处理
                    paramValue = value;
                }

                // 添加条件和参数
                whereConditions.Add($"{columnName} = {paramName}");
                parameters.Add(paramName, paramValue);
            }
        }

        // 构建SQL片段
        var whereClause = whereConditions.Any() ? $"WHERE {string.Join(" AND ", whereConditions)}" : "";
        var groupByClause = !string.IsNullOrEmpty(groupBy) ? $"GROUP BY {groupBy}" : "";

        // 确保排序有默认值
        var orderByClause = !string.IsNullOrEmpty(orderBy)
            ? $"ORDER BY {orderBy}"
            : $"ORDER BY {propertyMetadata.First().ColumnName} ASC";

        // 计算总记录数
        string countSql = $"SELECT COUNT(1) FROM {tableName} {whereClause} {groupByClause}";
        var totalCount = await connection.QueryFirstOrDefaultAsync<int>(countSql, parameters);

        // 计算分页参数
        int skip = (pageIndex - 1) * pageSize;
        string paginationClause = GetPaginationClause(dbtype, skip, pageSize);

        // 构建查询SQL
        var sqlBuilder = new StringBuilder();
        sqlBuilder.AppendLine($"SELECT {selectFields} FROM {tableName}");
        sqlBuilder.AppendLine(whereClause);
        sqlBuilder.AppendLine(groupByClause);
        sqlBuilder.AppendLine(orderByClause);
        sqlBuilder.AppendLine(paginationClause);

        // 执行查询
        var data = await connection.QueryAsync<T>(sqlBuilder.ToString(), parameters);

        // 构建返回结果
        result.SetSuccess(new
        {
            Total = totalCount,
            Rows = data
        });

        return result;
    }
    /// <summary>
    /// 
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="entity"></param>
    /// <param name="dbtype"></param>
    /// <param name="db"></param>
    /// <returns></returns>
    /// <exception cref="ArgumentNullException"></exception>
    /// <exception cref="InvalidOperationException"></exception>
    /// <exception cref="ArgumentException"></exception>
    public async Task<T> GetBaseService<T>(T entity,int is_deleted = 0,int dbtype = 0, string db = "DefaultConnection")
    {
        var result = new CommonResponse();

        // 参数验证
        if (entity == null)
            throw new ArgumentNullException(nameof(entity));

        using var connection = CreateConnection(dbtype, db);
        var type = typeof(T);

        // 获取表名
        var tableAttr = type.GetCustomAttribute<TableAttribute>();
        if (tableAttr == null || string.IsNullOrEmpty(tableAttr.Name))
            throw new InvalidOperationException($"实体 {type.Name} 未定义Table特性或表名");
        string tableName = tableAttr.Name;

        // 获取所有属性及其列名和值
        var properties = type.GetProperties()
            .Where(p => p.CanRead && p.CanWrite)
            .Select(p => new
            {
                Property = p,
                ColumnName = p.GetCustomAttribute<ColumnAttribute>()?.Name ?? p.Name,
                Value = p.GetValue(entity),
                DefaultValue = p.PropertyType.IsValueType ? Activator.CreateInstance(p.PropertyType) : null
            })
            .ToList();

        // 查找主键ID属性
        var idProperty = properties.FirstOrDefault(p =>
            string.Equals(p.ColumnName, "id", StringComparison.OrdinalIgnoreCase));

        if (idProperty == null)
            throw new InvalidOperationException($"实体 {type.Name} 未找到主键列（id）");

        // 检查ID是否提供（不能是默认值）
        if (Equals(idProperty.Value, idProperty.DefaultValue))
            throw new ArgumentException("必须提供ID值", nameof(entity));

        // 构建查询字段（默认查询所有字段）
        var selectFields = "*";

        // 构建查询SQL
        var sqlBuilder = new StringBuilder();
        sqlBuilder.AppendLine($"SELECT {selectFields} FROM {tableName} WHERE id = '{idProperty.Value}' and is_deleted = {is_deleted}");

        // 执行查询
        var data = await QueryFirstOrDefaultAsync<T>(sqlBuilder.ToString(), new SqlBuilder());

        return data;
    }

    // 辅助方法：根据数据库类型转义标识符
    private string EscapeSqlIdentifier(string identifier, int dbtype)
    {
        return dbtype switch
        {
            1 => $"[{identifier}]",      // SQL Server
            2 => $"\"{identifier}\"",    // Oracle
            _ => $"`{identifier}`"       // MySQL
        };
    }

    // 辅助方法：根据数据库类型生成分页语句
    private string GetPaginationClause(int dbtype, int skip, int pageSize)
    {
        return dbtype switch
        {
            1 => $"OFFSET {skip} ROWS FETCH NEXT {pageSize} ROWS ONLY",  // SQL Server
            2 => $"OFFSET {skip} ROWS FETCH NEXT {pageSize} ROWS ONLY",  // Oracle 12c+
            _ => $"LIMIT {pageSize} OFFSET {skip}"                       // MySQL
        };
    }


}